package com.youlai.boot.modules.distribution.service.impl;

import com.youlai.boot.common.util.ExcelUtils;
import com.youlai.boot.modules.distribution.dto.response.*;
import com.youlai.boot.modules.distribution.service.DistributionDashboardService;
import com.youlai.boot.modules.tp.dto.query.DateRangeQuery;
import com.youlai.boot.system.model.dto.BrandExcelDTO;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import jakarta.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

/**
 * 经销看板服务实现类
 * 
 * @author xiheforever
 * @since 2025-06-05
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class DistributionDashboardServiceImpl implements DistributionDashboardService {

    private final JdbcTemplate jdbcTemplate;

    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");
    private static final DateTimeFormatter DATETIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    // 颜色配置
    private static final String[] CHART_COLORS = {
        "#409eff", "#67c23a", "#e6a23c", "#f56c6c", "#909399",
        "#c45656", "#73767a", "#dd6161", "#409eff", "#67c23a"
    };

    @Override
    public DistributionRealTimeDataVO getRealTimeData() {
        log.info("获取经销看板实时数据");
        
        try {
            // 获取在线品牌数（最近一个月有数据的品牌）
            String onlineBrandsSql = """
                SELECT COUNT(DISTINCT brand_name)
                FROM brand
                WHERE is_deleted = 0
                AND time_period >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m')
                """;
            Integer onlineBrands = jdbcTemplate.queryForObject(onlineBrandsSql, Integer.class);

            // 获取实时访客数（最新时间周期的总访客数）
            String realtimeVisitorsSql = """
                SELECT COALESCE(SUM(visitor_count), 0)
                FROM brand
                WHERE is_deleted = 0
                AND time_period = (SELECT MAX(time_period) FROM brand WHERE is_deleted = 0)
                """;
            Integer realtimeVisitors = jdbcTemplate.queryForObject(realtimeVisitorsSql, Integer.class);

            // 获取今日成交金额（万元）- 使用最新时间周期数据
            String todayAmountSql = """
                SELECT COALESCE(SUM(transaction_amount), 0) / 1000000
                FROM brand
                WHERE is_deleted = 0
                AND time_period = (SELECT MAX(time_period) FROM brand WHERE is_deleted = 0)
                """;
            Double todayAmount = jdbcTemplate.queryForObject(todayAmountSql, Double.class);

            // 获取今日成交单数
            String todayOrdersSql = """
                SELECT COALESCE(SUM(transaction_order_count), 0)
                FROM brand
                WHERE is_deleted = 0
                AND time_period = (SELECT MAX(time_period) FROM brand WHERE is_deleted = 0)
                """;
            Integer todayOrders = jdbcTemplate.queryForObject(todayOrdersSql, Integer.class);

            // 计算实时转化率
            String conversionSql = """
                SELECT
                    CASE
                        WHEN SUM(visitor_count) > 0
                        THEN (SUM(transaction_user_count) * 100.0 / SUM(visitor_count))
                        ELSE 0
                    END as conversion_rate
                FROM brand
                WHERE is_deleted = 0
                AND time_period = (SELECT MAX(time_period) FROM brand WHERE is_deleted = 0)
                """;
            Double realtimeConversionRate = jdbcTemplate.queryForObject(conversionSql, Double.class);

            return DistributionRealTimeDataVO.builder()
                .onlineBrands(onlineBrands != null ? onlineBrands : 0)
                .realtimeVisitors(realtimeVisitors != null ? realtimeVisitors : 0)
                .todayAmount(todayAmount != null ? todayAmount : 0.0)
                .todayOrders(todayOrders != null ? todayOrders : 0)
                .realtimeConversionRate(realtimeConversionRate != null ? realtimeConversionRate : 0.0)
                .lastUpdateTime(LocalDateTime.now().format(DATETIME_FORMATTER))
                .build();

        } catch (Exception e) {
            log.error("获取实时数据失败", e);
            return DistributionRealTimeDataVO.builder()
                .onlineBrands(0)
                .realtimeVisitors(0)
                .todayAmount(0.0)
                .todayOrders(0)
                .realtimeConversionRate(0.0)
                .lastUpdateTime(LocalDateTime.now().format(DATETIME_FORMATTER))
                .build();
        }
    }

    @Override
    public DistributionOverviewMetricsVO getOverviewMetrics(DateRangeQuery dateRange) {
        log.info("获取经销看板概览指标，日期范围: {}", dateRange);
        
        try {
            // 构建日期条件
            String dateCondition = buildDateCondition(dateRange);
            String previousDateCondition = buildPreviousDateCondition(dateRange);

            // 获取总品牌数
            String totalBrandsSql = String.format("""
                SELECT COUNT(DISTINCT brand_name)
                FROM brand
                WHERE is_deleted = 0 %s
                """, dateCondition);
            Integer totalBrands = jdbcTemplate.queryForObject(totalBrandsSql, Integer.class);

            // 获取上期品牌数计算增长率
            String previousBrandsSql = String.format("""
                SELECT COUNT(DISTINCT brand_name)
                FROM brand
                WHERE is_deleted = 0 %s
                """, previousDateCondition);
            Integer previousBrands = jdbcTemplate.queryForObject(previousBrandsSql, Integer.class);
            
            Double brandGrowthRate = calculateGrowthRate(totalBrands, previousBrands);

            // 获取活跃品牌数（有成交的品牌）
            String activeBrandsSql = String.format("""
                SELECT COUNT(DISTINCT brand_name)
                FROM brand
                WHERE transaction_amount > 0 AND is_deleted = 0 %s
                """, dateCondition);
            Integer activeBrands = jdbcTemplate.queryForObject(activeBrandsSql, Integer.class);

            // 计算活跃品牌率
            Double activeBrandRate = totalBrands > 0 ? 
                (activeBrands * 100.0 / totalBrands) : 0.0;

            // 获取总成交金额（万元）
            String totalAmountSql = String.format("""
                SELECT COALESCE(SUM(transaction_amount), 0) / 1000000 
                FROM brand 
                WHERE is_deleted = 0 %s
                """, dateCondition);
            Double totalTransactionAmount = jdbcTemplate.queryForObject(totalAmountSql, Double.class);

            // 获取上期成交金额计算增长率
            String previousAmountSql = String.format("""
                SELECT COALESCE(SUM(transaction_amount), 0) / 1000000 
                FROM brand 
                WHERE is_deleted = 0 %s
                """, previousDateCondition);
            Double previousAmount = jdbcTemplate.queryForObject(previousAmountSql, Double.class);
            
            Double amountGrowthRate = calculateGrowthRate(totalTransactionAmount, previousAmount);

            // 获取平均客单价
            String avgTransactionValueSql = String.format("""
                SELECT 
                    CASE 
                        WHEN SUM(transaction_user_count) > 0 
                        THEN SUM(transaction_amount) / SUM(transaction_user_count) / 100
                        ELSE 0 
                    END as avg_value
                FROM brand 
                WHERE is_deleted = 0 %s
                """, dateCondition);
            Double avgTransactionValue = jdbcTemplate.queryForObject(avgTransactionValueSql, Double.class);

            // 获取整体转化率
            String overallConversionSql = String.format("""
                SELECT 
                    CASE 
                        WHEN SUM(visitor_count) > 0 
                        THEN (SUM(transaction_user_count) * 100.0 / SUM(visitor_count))
                        ELSE 0 
                    END as conversion_rate
                FROM brand 
                WHERE is_deleted = 0 %s
                """, dateCondition);
            Double overallConversionRate = jdbcTemplate.queryForObject(overallConversionSql, Double.class);

            return DistributionOverviewMetricsVO.builder()
                .totalBrands(totalBrands != null ? totalBrands : 0)
                .brandGrowthRate(brandGrowthRate)
                .activeBrands(activeBrands != null ? activeBrands : 0)
                .activeBrandRate(activeBrandRate)
                .totalTransactionAmount(totalTransactionAmount != null ? totalTransactionAmount : 0.0)
                .amountGrowthRate(amountGrowthRate)
                .avgTransactionValue(avgTransactionValue != null ? avgTransactionValue : 0.0)
                .overallConversionRate(overallConversionRate != null ? overallConversionRate : 0.0)
                .build();

        } catch (Exception e) {
            log.error("获取概览指标失败", e);
            return DistributionOverviewMetricsVO.builder()
                .totalBrands(0)
                .brandGrowthRate(0.0)
                .activeBrands(0)
                .activeBrandRate(0.0)
                .totalTransactionAmount(0.0)
                .amountGrowthRate(0.0)
                .avgTransactionValue(0.0)
                .overallConversionRate(0.0)
                .build();
        }
    }

    @Override
    public BrandPerformancePageVO getBrandPerformanceList(BrandPerformanceQueryVO query) {
        log.info("获取品牌表现列表，查询参数: {}", query);
        
        try {
            // 构建查询条件
            StringBuilder whereClause = new StringBuilder(" WHERE is_deleted = 0 ");
            List<Object> params = new ArrayList<>();

            // 日期条件 - 使用time_period字段
            if (query.getStartDate() != null) {
                String startMonth = query.getStartDate().format(DateTimeFormatter.ofPattern("yyyy-MM"));
                whereClause.append(" AND time_period >= ? ");
                params.add(startMonth);
                log.info("添加开始日期条件: time_period >= {}", startMonth);
            }
            if (query.getEndDate() != null) {
                String endMonth = query.getEndDate().format(DateTimeFormatter.ofPattern("yyyy-MM"));
                whereClause.append(" AND time_period <= ? ");
                params.add(endMonth);
                log.info("添加结束日期条件: time_period <= {}", endMonth);
            }

            // 品牌名称条件
            if (query.getBrandName() != null && !query.getBrandName().trim().isEmpty()) {
                whereClause.append(" AND brand_name LIKE ? ");
                params.add("%" + query.getBrandName().trim() + "%");
                log.info("添加品牌名称条件: brand_name LIKE %{}%", query.getBrandName().trim());
            }

            // 经营模式条件
            if (query.getOperationMode() != null && !query.getOperationMode().trim().isEmpty()) {
                whereClause.append(" AND operation_mode = ? ");
                params.add(query.getOperationMode().trim());
                log.info("添加经营模式条件: operation_mode = {}", query.getOperationMode().trim());
            }

            // 一级类目条件
            if (query.getFirstCategory() != null && !query.getFirstCategory().trim().isEmpty()) {
                whereClause.append(" AND first_category = ? ");
                params.add(query.getFirstCategory().trim());
                log.info("添加一级类目条件: first_category = {}", query.getFirstCategory().trim());
            }

            // 统计总数
            String countSql = "SELECT COUNT(*) FROM brand " + whereClause.toString();
            log.info("执行统计SQL: {}", countSql);
            log.info("统计SQL参数: {}", params);
            Long total = jdbcTemplate.queryForObject(countSql, params.toArray(), Long.class);
            log.info("统计结果: 总记录数 = {}", total);

            // 分页参数处理
            int pageNum = query.getPageNum() != null && query.getPageNum() > 0 ? query.getPageNum() : 1;
            int pageSize = query.getPageSize() != null && query.getPageSize() > 0 ? query.getPageSize() : 10;
            int offset = (pageNum - 1) * pageSize;
            log.info("分页参数: pageNum={}, pageSize={}, offset={}", pageNum, pageSize, offset);

            // 分页查询 - 增加update_time字段
            String dataSql = String.format("""
                SELECT 
                    brand_name,
                    first_category,
                    second_category,
                    third_category,
                    operation_mode,
                    page_views,
                    visitor_count,
                    avg_page_views_per_visitor,
                    avg_stay_time,
                    transaction_user_count,
                    conversion_rate,
                    transaction_order_count,
                    transaction_item_count,
                    transaction_amount / 1000000 as transaction_amount,
                    average_transaction_value / 100 as average_transaction_value,
                    time_period,
                    update_time
                FROM brand %s
                ORDER BY transaction_amount DESC
                LIMIT %d OFFSET %d
                """, whereClause.toString(), pageSize, offset);
            
            log.info("执行分页查询SQL: {}", dataSql);
            log.info("分页查询SQL参数: {}", params);

            List<BrandPerformancePageVO.BrandPerformanceDataVO> list = jdbcTemplate.query(
                dataSql, 
                params.toArray(),
                (rs, rowNum) -> {
                    // 计算真实的增长率（这里简化处理，实际应该查询同比数据）
                    double growthRate = (Math.random() - 0.5) * 40; // 临时模拟，后续可以基于历史数据计算
                    String trendIcon = growthRate > 5 ? "up" : 
                                     growthRate < -5 ? "down" : "stable";

                    return BrandPerformancePageVO.BrandPerformanceDataVO.builder()
                        .brandName(rs.getString("brand_name"))
                        .mainCategory(rs.getString("first_category"))
                        .firstCategory(rs.getString("first_category"))
                        .secondCategory(rs.getString("second_category"))
                        .thirdCategory(rs.getString("third_category"))
                        .operationMode(rs.getString("operation_mode"))
                        .pageViews(rs.getInt("page_views"))
                        .visitorCount(rs.getInt("visitor_count"))
                        .avgPageViewsPerVisitor(rs.getBigDecimal("avg_page_views_per_visitor"))
                        .avgStayTime(rs.getInt("avg_stay_time"))
                        .transactionUserCount(rs.getInt("transaction_user_count"))
                        .conversionRate(rs.getBigDecimal("conversion_rate"))
                        .transactionOrderCount(rs.getInt("transaction_order_count"))
                        .transactionItemCount(rs.getInt("transaction_item_count"))
                        .transactionAmount(rs.getBigDecimal("transaction_amount"))
                        .averageTransactionValue(rs.getBigDecimal("average_transaction_value"))
                        .updateTime(rs.getTimestamp("update_time"))
                        .growthRate(BigDecimal.valueOf(growthRate).setScale(2, RoundingMode.HALF_UP))
                        .trendIcon(trendIcon)
                        .build();
                }
            );

            // 确保分页信息正确
            log.info("品牌表现列表查询完成 - 总数: {}, 当前页: {}, 页大小: {}, 实际返回: {}", 
                total, pageNum, pageSize, list.size());

            return BrandPerformancePageVO.builder()
                .list(list)
                .total(total != null ? total : 0L)
                .build();

        } catch (Exception e) {
            log.error("获取品牌表现列表失败", e);
            return BrandPerformancePageVO.builder()
                .list(new ArrayList<>())
                .total(0L)
                .build();
        }
    }

    @Override
    public CategoryAnalysisResponseVO getCategoryAnalysis(DateRangeQuery dateRange, Integer categoryLevel) {
        log.info("获取类目分析，日期范围: {}，类目级别: {}", dateRange, categoryLevel);
        
        try {
            String dateCondition = buildDateCondition(dateRange);
            String categoryField = getCategoryField(categoryLevel);

            String sql = String.format("""
                SELECT 
                    %s as category_name,
                    COUNT(DISTINCT brand_name) as brand_count,
                    SUM(transaction_amount) / 1000000 as transaction_amount,
                    AVG(conversion_rate) as avg_conversion_rate
                FROM brand 
                WHERE is_deleted = 0 %s
                GROUP BY %s 
                ORDER BY transaction_amount DESC
                """, categoryField, dateCondition, categoryField);

            List<CategoryAnalysisResponseVO.CategoryAnalysisDataVO> dataList = jdbcTemplate.query(sql, (rs, rowNum) -> {
                return CategoryAnalysisResponseVO.CategoryAnalysisDataVO.builder()
                    .categoryName(rs.getString("category_name"))
                    .brandCount(rs.getInt("brand_count"))
                    .transactionAmount(rs.getBigDecimal("transaction_amount"))
                    .avgConversionRate(rs.getBigDecimal("avg_conversion_rate"))
                    .color(CHART_COLORS[rowNum % CHART_COLORS.length])
                    .build();
            });

            // 计算占比
            if (!dataList.isEmpty()) {
                int totalBrands = dataList.stream().mapToInt(CategoryAnalysisResponseVO.CategoryAnalysisDataVO::getBrandCount).sum();
                BigDecimal totalAmount = dataList.stream()
                    .map(CategoryAnalysisResponseVO.CategoryAnalysisDataVO::getTransactionAmount)
                    .reduce(BigDecimal.ZERO, BigDecimal::add);

                dataList.forEach(item -> {
                    if (totalBrands > 0) {
                        BigDecimal brandRatio = BigDecimal.valueOf(item.getBrandCount() * 100.0 / totalBrands)
                            .setScale(2, RoundingMode.HALF_UP);
                        item.setBrandRatio(brandRatio);
                    }
                    
                    if (totalAmount.compareTo(BigDecimal.ZERO) > 0) {
                        BigDecimal amountRatio = item.getTransactionAmount()
                            .multiply(BigDecimal.valueOf(100))
                            .divide(totalAmount, 2, RoundingMode.HALF_UP);
                        item.setAmountRatio(amountRatio);
                    }
                });
            }

            return CategoryAnalysisResponseVO.builder()
                .data(dataList)
                .build();

        } catch (Exception e) {
            log.error("获取类目分析失败", e);
            return CategoryAnalysisResponseVO.builder()
                .data(new ArrayList<>())
                .build();
        }
    }

    @Override
    public OperationModeAnalysisResponseVO getOperationModeAnalysis(DateRangeQuery dateRange) {
        log.info("获取经营模式分析，日期范围: {}", dateRange);
        
        try {
            String dateCondition = buildDateCondition(dateRange);

            String sql = String.format("""
                SELECT 
                    operation_mode,
                    COUNT(DISTINCT brand_name) as brand_count,
                    SUM(transaction_amount) / 1000000 as transaction_amount,
                    AVG(conversion_rate) as avg_conversion_rate
                FROM brand 
                WHERE is_deleted = 0 %s
                GROUP BY operation_mode 
                ORDER BY transaction_amount DESC
                """, dateCondition);

            List<OperationModeAnalysisResponseVO.OperationModeAnalysisDataVO> dataList = 
                jdbcTemplate.query(sql, (rs, rowNum) -> {
                    return OperationModeAnalysisResponseVO.OperationModeAnalysisDataVO.builder()
                        .operationMode(rs.getString("operation_mode"))
                        .brandCount(rs.getInt("brand_count"))
                        .transactionAmount(rs.getBigDecimal("transaction_amount"))
                        .avgConversionRate(rs.getBigDecimal("avg_conversion_rate"))
                        .color(CHART_COLORS[rowNum % CHART_COLORS.length])
                        .build();
                });

            // 计算占比
            if (!dataList.isEmpty()) {
                int totalBrands = dataList.stream().mapToInt(OperationModeAnalysisResponseVO.OperationModeAnalysisDataVO::getBrandCount).sum();
                BigDecimal totalAmount = dataList.stream()
                    .map(OperationModeAnalysisResponseVO.OperationModeAnalysisDataVO::getTransactionAmount)
                    .reduce(BigDecimal.ZERO, BigDecimal::add);

                dataList.forEach(item -> {
                    if (totalBrands > 0) {
                        BigDecimal brandRatio = BigDecimal.valueOf(item.getBrandCount() * 100.0 / totalBrands)
                            .setScale(2, RoundingMode.HALF_UP);
                        item.setBrandRatio(brandRatio);
                    }
                    
                    if (totalAmount.compareTo(BigDecimal.ZERO) > 0) {
                        BigDecimal amountRatio = item.getTransactionAmount()
                            .multiply(BigDecimal.valueOf(100))
                            .divide(totalAmount, 2, RoundingMode.HALF_UP);
                        item.setAmountRatio(amountRatio);
                    }
                });
            }

            return OperationModeAnalysisResponseVO.builder()
                .data(dataList)
                .build();

        } catch (Exception e) {
            log.error("获取经营模式分析失败", e);
            return OperationModeAnalysisResponseVO.builder()
                .data(new ArrayList<>())
                .build();
        }
    }

    @Override
    public BrandRankingResponseVO getBrandRanking(DateRangeQuery dateRange, Integer limit) {
        log.info("获取品牌排行，日期范围: {}，限制数量: {}", dateRange, limit);
        
        try {
            String dateCondition = buildDateCondition(dateRange);
            int rankingLimit = limit != null ? limit : 10;

            String sql = String.format("""
                SELECT 
                    brand_name,
                    first_category as main_category,
                    SUM(transaction_amount) / 1000000 as transaction_amount
                FROM brand 
                WHERE is_deleted = 0 %s
                GROUP BY brand_name, first_category
                ORDER BY transaction_amount DESC
                LIMIT %d
                """, dateCondition, rankingLimit);

            List<BrandRankingResponseVO.BrandRankingDataVO> dataList = jdbcTemplate.query(sql, (rs, rowNum) -> {
                // 模拟增长率和趋势
                double mockGrowthRate = (Math.random() - 0.5) * 40;
                String trendIcon = mockGrowthRate > 5 ? "up" : 
                                 mockGrowthRate < -5 ? "down" : "stable";

                return BrandRankingResponseVO.BrandRankingDataVO.builder()
                    .brandName(rs.getString("brand_name"))
                    .mainCategory(rs.getString("main_category"))
                    .transactionAmount(rs.getBigDecimal("transaction_amount"))
                    .growthRate(BigDecimal.valueOf(mockGrowthRate).setScale(2, RoundingMode.HALF_UP))
                    .trendIcon(trendIcon)
                    .rank(rowNum + 1)
                    .build();
            });

            return BrandRankingResponseVO.builder()
                .data(dataList)
                .build();

        } catch (Exception e) {
            log.error("获取品牌排行失败", e);
            return BrandRankingResponseVO.builder()
                .data(new ArrayList<>())
                .build();
        }
    }

    @Override
    public TrendDataResponseVO getAmountTrend(DateRangeQuery dateRange) {
        log.info("获取成交金额趋势，日期范围: {}", dateRange);
        
        try {
            String sql = """
                SELECT 
                    time_period as date,
                    SUM(transaction_amount) / 1000000 as transaction_amount
                FROM brand 
                WHERE is_deleted = 0 
                """ + buildDateCondition(dateRange) + """
                GROUP BY time_period
                ORDER BY time_period ASC
                """;

            List<TrendDataResponseVO.TrendDataPointVO> dataList = jdbcTemplate.query(sql, (rs, rowNum) -> {
                return TrendDataResponseVO.TrendDataPointVO.builder()
                    .date(rs.getString("date"))
                    .transactionAmount(BigDecimal.valueOf(rs.getDouble("transaction_amount"))
                        .setScale(2, RoundingMode.HALF_UP))
                    .brandCount(null)
                    .conversionRate(null)
                    .build();
            });

            return TrendDataResponseVO.builder()
                .data(dataList)
                .build();

        } catch (Exception e) {
            log.error("获取成交金额趋势失败", e);
            return TrendDataResponseVO.builder()
                .data(new ArrayList<>())
                .build();
        }
    }

    @Override
    public TrendDataResponseVO getBrandCountTrend(DateRangeQuery dateRange) {
        log.info("获取品牌数量趋势，日期范围: {}", dateRange);
        
        try {
            String sql = """
                SELECT 
                    time_period as date,
                    COUNT(DISTINCT brand_name) as brand_count
                FROM brand 
                WHERE is_deleted = 0 
                """ + buildDateCondition(dateRange) + """
                GROUP BY time_period
                ORDER BY time_period ASC
                """;

            List<TrendDataResponseVO.TrendDataPointVO> dataList = jdbcTemplate.query(sql, (rs, rowNum) -> {
                return TrendDataResponseVO.TrendDataPointVO.builder()
                    .date(rs.getString("date"))
                    .transactionAmount(null)
                    .brandCount(rs.getInt("brand_count"))
                    .conversionRate(null)
                    .build();
            });

            return TrendDataResponseVO.builder()
                .data(dataList)
                .build();

        } catch (Exception e) {
            log.error("获取品牌数量趋势失败", e);
            return TrendDataResponseVO.builder()
                .data(new ArrayList<>())
                .build();
        }
    }

    @Override
    public TrendDataResponseVO getConversionTrend(DateRangeQuery dateRange) {
        log.info("获取转化率趋势，日期范围: {}", dateRange);
        
        try {
            String sql = """
                SELECT 
                    time_period as date,
                    CASE 
                        WHEN SUM(visitor_count) > 0 
                        THEN (SUM(transaction_user_count) * 100.0 / SUM(visitor_count))
                        ELSE 0 
                    END as conversion_rate
                FROM brand 
                WHERE is_deleted = 0 
                """ + buildDateCondition(dateRange) + """
                GROUP BY time_period
                ORDER BY time_period ASC
                """;

            List<TrendDataResponseVO.TrendDataPointVO> dataList = jdbcTemplate.query(sql, (rs, rowNum) -> {
                return TrendDataResponseVO.TrendDataPointVO.builder()
                    .date(rs.getString("date"))
                    .transactionAmount(null)
                    .brandCount(null)
                    .conversionRate(BigDecimal.valueOf(rs.getDouble("conversion_rate"))
                        .setScale(2, RoundingMode.HALF_UP))
                    .build();
            });

            return TrendDataResponseVO.builder()
                .data(dataList)
                .build();

        } catch (Exception e) {
            log.error("获取转化率趋势失败", e);
            return TrendDataResponseVO.builder()
                .data(new ArrayList<>())
                .build();
        }
    }

    @Override
    public DistributionDashboardAllDataVO getAllData(DateRangeQuery dateRange) {
        log.info("获取看板完整数据，日期范围: {}", dateRange);
        
        try {
            return DistributionDashboardAllDataVO.builder()
                .realtimeData(getRealTimeData())
                .overviewMetrics(getOverviewMetrics(dateRange))
                .brandRanking(getBrandRanking(dateRange, 10))
                .operationModeAnalysis(getOperationModeAnalysis(dateRange))
                .amountTrend(getAmountTrend(dateRange))
                .brandCountTrend(getBrandCountTrend(dateRange))
                .conversionTrend(getConversionTrend(dateRange))
                .build();

        } catch (Exception e) {
            log.error("获取看板完整数据失败", e);
            return DistributionDashboardAllDataVO.builder()
                .realtimeData(getRealTimeData())
                .overviewMetrics(getOverviewMetrics(dateRange))
                .brandRanking(BrandRankingResponseVO.builder().data(new ArrayList<>()).build())
                .operationModeAnalysis(OperationModeAnalysisResponseVO.builder().data(new ArrayList<>()).build())
                .amountTrend(TrendDataResponseVO.builder().data(new ArrayList<>()).build())
                .brandCountTrend(TrendDataResponseVO.builder().data(new ArrayList<>()).build())
                .conversionTrend(TrendDataResponseVO.builder().data(new ArrayList<>()).build())
                .build();
        }
    }

    @Override
    public void exportBrandPerformance(BrandPerformanceQueryVO query, HttpServletResponse response) {
        log.info("导出品牌表现数据，查询参数: {}", query);
        
        try {
            // 构建查询条件
            StringBuilder whereCondition = new StringBuilder(" WHERE is_deleted = 0 ");
            List<Object> params = new ArrayList<>();
            
            // 日期范围条件
            if (query != null) {
                if (query.getStartDate() != null) {
                    whereCondition.append(" AND time_period >= ? ");
                    params.add(query.getStartDate().format(DateTimeFormatter.ofPattern("yyyy-MM")));
                }
                if (query.getEndDate() != null) {
                    whereCondition.append(" AND time_period <= ? ");
                    params.add(query.getEndDate().format(DateTimeFormatter.ofPattern("yyyy-MM")));
                }
                
                // 品牌名称条件
                if (query.getBrandName() != null && !query.getBrandName().trim().isEmpty()) {
                    whereCondition.append(" AND brand_name LIKE ? ");
                    params.add("%" + query.getBrandName().trim() + "%");
                }
                
                // 一级类目条件
                if (query.getFirstCategory() != null && !query.getFirstCategory().trim().isEmpty()) {
                    whereCondition.append(" AND first_category = ? ");
                    params.add(query.getFirstCategory().trim());
                }
                
                // 经营模式条件
                if (query.getOperationMode() != null && !query.getOperationMode().trim().isEmpty()) {
                    whereCondition.append(" AND operation_mode = ? ");
                    params.add(query.getOperationMode().trim());
                }
            }
            
            // 查询品牌表现数据
            String sql = """
                SELECT 
                    time_period as timePeriod,
                    brand_name as brandName,
                    first_category as firstCategory,
                    second_category as secondCategory,
                    third_category as thirdCategory,
                    operation_mode as operationMode,
                    page_views as pageViews,
                    visitor_count as visitorCount,
                    avg_page_views_per_visitor as avgPageViewsPerVisitor,
                    avg_stay_time as avgStayTime,
                    transaction_user_count as transactionUserCount,
                    conversion_rate as conversionRate,
                    transaction_order_count as transactionOrderCount,
                    transaction_item_count as transactionItemCount,
                    transaction_amount / 1000000 as transactionAmount,
                    average_transaction_value / 100 as averageTransactionValue
                FROM brand 
                """ + whereCondition.toString() + """
                ORDER BY transaction_amount DESC
                """;

            List<BrandExcelDTO> excelData = jdbcTemplate.query(sql, params.toArray(), (rs, rowNum) -> {
                BrandExcelDTO dto = new BrandExcelDTO();
                dto.setTimePeriod(rs.getString("timePeriod"));
                dto.setBrandName(rs.getString("brandName"));
                dto.setFirstCategory(rs.getString("firstCategory"));
                dto.setSecondCategory(rs.getString("secondCategory"));
                dto.setThirdCategory(rs.getString("thirdCategory"));
                dto.setOperationMode(rs.getString("operationMode"));
                dto.setPageViews(rs.getInt("pageViews"));
                dto.setVisitorCount(rs.getInt("visitorCount"));
                dto.setAvgPageViewsPerVisitor(rs.getBigDecimal("avgPageViewsPerVisitor"));
                dto.setAvgStayTime(rs.getBigDecimal("avgStayTime"));
                dto.setTransactionUserCount(rs.getInt("transactionUserCount"));
                dto.setConversionRate(rs.getBigDecimal("conversionRate"));
                dto.setTransactionOrderCount(rs.getInt("transactionOrderCount"));
                dto.setTransactionItemCount(rs.getInt("transactionItemCount"));
                dto.setTransactionAmount(rs.getBigDecimal("transactionAmount"));
                dto.setAverageTransactionValue(rs.getBigDecimal("averageTransactionValue"));
                return dto;
            });
            
            // 生成文件名
            String fileName = "品牌表现数据_" + 
                             LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss"));
            
            // 导出Excel
            ExcelUtils.exportExcel(response, excelData, BrandExcelDTO.class, fileName, "品牌表现数据");
            
            log.info("品牌表现数据导出完成，数据条数: {}", excelData.size());
            
        } catch (Exception e) {
            log.error("导出品牌表现数据失败", e);
            throw new RuntimeException("导出失败: " + e.getMessage());
        }
    }

    @Override
    public List<String> getFirstCategoryOptions() {
        try {
            String sql = "SELECT DISTINCT first_category FROM brand WHERE is_deleted = 0 ORDER BY first_category";
            return jdbcTemplate.queryForList(sql, String.class);
        } catch (Exception e) {
            log.error("获取一级类目选项失败", e);
            return List.of();
        }
    }

    @Override
    public List<String> getOperationModeOptions() {
        try {
            String sql = "SELECT DISTINCT operation_mode FROM brand WHERE is_deleted = 0 ORDER BY operation_mode";
            return jdbcTemplate.queryForList(sql, String.class);
        } catch (Exception e) {
            log.error("获取经营模式选项失败", e);
            return List.of();
        }
    }

    @Override
    public List<String> getBrandOptions() {
        try {
            String sql = "SELECT DISTINCT brand_name FROM brand WHERE is_deleted = 0 ORDER BY brand_name";
            return jdbcTemplate.queryForList(sql, String.class);
        } catch (Exception e) {
            log.error("获取品牌选项失败", e);
            return List.of();
        }
    }

    // 工具方法

    /**
     * 构建日期条件
     */
    private String buildDateCondition(DateRangeQuery dateRange) {
        if (dateRange == null) {
            return "";
        }
        
        StringBuilder condition = new StringBuilder();
        
        if (dateRange.getStartDate() != null) {
            // 使用时间周期字段进行过滤，转换为YYYY-MM格式
            String startMonth = dateRange.getStartDate().format(DateTimeFormatter.ofPattern("yyyy-MM"));
            condition.append(" AND time_period >= '")
                .append(startMonth)
                .append("'");
        }
        
        if (dateRange.getEndDate() != null) {
            // 使用时间周期字段进行过滤，转换为YYYY-MM格式
            String endMonth = dateRange.getEndDate().format(DateTimeFormatter.ofPattern("yyyy-MM"));
            condition.append(" AND time_period <= '")
                .append(endMonth)
                .append("'");
        }
        
        return condition.toString();
    }

    /**
     * 构建上期日期条件（用于计算增长率）
     */
    private String buildPreviousDateCondition(DateRangeQuery dateRange) {
        if (dateRange == null || dateRange.getStartDate() == null || dateRange.getEndDate() == null) {
            return "";
        }
        
        // 计算时间跨度（月份）
        long months = dateRange.getEndDate().withDayOfMonth(1)
            .until(dateRange.getStartDate().withDayOfMonth(1))
            .getMonths();
        if (months == 0) months = 1; // 至少1个月
        
        // 上期开始和结束月份
        LocalDate previousStart = dateRange.getStartDate().minusMonths(months);
        LocalDate previousEnd = dateRange.getStartDate().minusMonths(1);
        
        String startMonth = previousStart.format(DateTimeFormatter.ofPattern("yyyy-MM"));
        String endMonth = previousEnd.format(DateTimeFormatter.ofPattern("yyyy-MM"));
        
        return String.format(" AND time_period >= '%s' AND time_period <= '%s'",
            startMonth, endMonth);
    }

    /**
     * 获取类目字段名
     */
    private String getCategoryField(Integer categoryLevel) {
        return switch (categoryLevel != null ? categoryLevel : 1) {
            case 2 -> "second_category";
            case 3 -> "third_category";
            default -> "first_category";
        };
    }

    /**
     * 计算增长率
     */
    private Double calculateGrowthRate(Number current, Number previous) {
        if (current == null || previous == null) {
            return 0.0;
        }
        
        double currentValue = current.doubleValue();
        double previousValue = previous.doubleValue();
        
        if (previousValue == 0) {
            return currentValue > 0 ? 100.0 : 0.0;
        }
        
        return ((currentValue - previousValue) / previousValue) * 100.0;
    }
} 